<?php

class Subscription_m extends CI_Model {
    public function __construct() {
        parent::__construct();
        $this->load->database();
        $this->load->helper('date');
    }
    
    function get_subscribers($group_id) {
        $this->db->select('user.*');
        $this->db->from('user');
        $this->db->join('subscriber', 'user.user_id = subscriber.user_id');
        $this->db->where('subscriber.group_id', $group_id);
        $query = $this->db->get();

        return $query->result_array();
    }
    
    
    function get_user_id($idno) {
        $this->load->helper();
        $this->db->select('user_id');
        $this->db->from('user');
        $this->db->where('idno', $idno);
        
        $query = $this->db->get();

        return $query->row_data();
    }
    
    
    function insert_subscriber($group_id, $selected_subs, $is_forced) {
        $this->db->where('group_id', $group_id);
        $this->db->delete('subscriber');
        date_default_timezone_set('Asia/Manila');
        
        foreach($selected_subs as $user) {
            $data = array(
                        'user_id' => $user,
                        'group_id' => $group_id,
                        'is_forced' => $is_forced,
                        'date_subscribed' => date('Y-m-d H:i:s',now())
            );
            
            $this->db->insert('subscriber', $data);
        }
    }
    
    
    function delete_subsciber($group_id, $selected_unsubs) {
        foreach($selected_unsubs as $user) {
            $data = array(
                        'user_id' => $user,
                        'group_id' => $group_id,
            );
            
            $where = "group_id = $group_id AND user_id = $user";
            $this->db->where($where);
            $this->db->delete('subscriber');
        }
    }
    
    function get_all_unsubscribed_students($group_id) {
        $query = "SELECT user_id, fname, lname FROM user 
                  WHERE user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id)
                  ORDER BY fname";
        
        $q = $this->db->query($query);
        
        return $q->result_array();
    }
        
    
    function get_all_authors() {
        $forbid_role = "viewer";
        $this->db->from('user');
        $this->db->join('user_role', 'user_role.user_id = user.user_id');
        $this->db->join('role', 'user_role.role_id = role.role_id');
        $this->db->where("user_role.role_id = 2 OR role.rolename = 'author'
            AND user.status != 'deleted'");
        $this->db->order_by('fname');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_groups_subscribed_by($user_id) {
        $this->db->select('groups.*, subscriber.*');
        $this->db->from('groups');
        $this->db->join('subscriber', 'subscriber.group_id = groups.group_id');
        $this->db->join('user', 'user.user_id = subscriber.user_id');
        $where = "user.user_id = $user_id AND groups.status != 'deleted'";
        $this->db->where($where);
        $this->db->order_by('subscriber.date_subscribed');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_groups_subscribed_by_order_name($user_id) {
        $this->db->select('groups.*, subscriber.*');
        $this->db->from('groups');
        $this->db->join('subscriber', 'subscriber.group_id = groups.group_id');
        $this->db->join('user', 'user.user_id = subscriber.user_id');
        $where = "user.user_id = $user_id AND groups.status != 'deleted'";
        $this->db->where($where);
        $this->db->order_by('groups.group_name ASC');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_groups_subscribed_by_order_name_like($user_id, $search_string) {
        $this->db->select('groups.*, subscriber.*');
        $this->db->from('groups');
        $this->db->join('subscriber', 'subscriber.group_id = groups.group_id');
        $this->db->join('user', 'user.user_id = subscriber.user_id');
        $where = "user.user_id = $user_id AND groups.status != 'deleted'";
        $this->db->where($where);
        $this->db->like('groups.group_name', $search_string);
        $this->db->order_by('groups.group_name ASC');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_groups_notby($user_id) {
        $this->db->from('groups');
        $this->db->where("`groups`.`group_id` NOT IN (SELECT `group_id` FROM
            `subscriber` NATURAL JOIN `groups`
            WHERE `subscriber`.`user_id` = $user_id) AND
            groups.is_subscribable = 1 AND groups.status != 'deleted'");
        $this->db->order_by('groups.group_name');
        return $this->db->get()->result_array();
    }
    
    function get_groups_notby_like($user_id, $search_string) {
        $this->db->from('groups');
        $this->db->where("`groups`.`group_id` NOT IN (SELECT `group_id` FROM
            `subscriber` NATURAL JOIN `groups`
            WHERE `subscriber`.`user_id` = $user_id) AND
            groups.is_subscribable = 1 AND groups.status != 'deleted'");
        $this->db->like('group_name', $search_string);
        $this->db->order_by('groups.group_name');
        return $this->db->get()->result_array();
    }
    
    function get_subscriber_name($user_id) {
        $this->db->select('user_id, lname, fname, mname');
        $this->db->from('user');
        $this->db->where('user_id', $user_id);
        $query = $this->db->get();
        
        return $query->row_array();
    }
        
    //subscription functions
    function get_all_not_subscribed($group_id) {
        $query = "SELECT lname, fname, mname, user_id
                    FROM user
                    WHERE status != 'deleted'
                    AND user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id)";
        
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    function get_all_students_only($group_id) {
        $query = "SELECT user_id, lname, fname, mname
                    FROM user
                    WHERE course IS NOT NULL AND status != 'deleted'
                    AND user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id)";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    function get_all_employees_only($group_id) {
        $query = "SELECT user_id, lname, fname, mname
                    FROM user
                    WHERE department IS NOT NULL AND status != 'deleted'
                    AND user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id)";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    function get_all_admins($group_id) {
        $query = "SELECT user_id, lname, fname, mname
                    FROM user
                    NATURAL JOIN user_role
                    NATURAL JOIN role
                    WHERE (role.role_id = 1 OR role.rolename = 'admin')
                        AND status != 'deleted'
                        AND user.user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id)";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    
    function get_user($user_id, $group_id) { //or user name
        $query = "SELECT user_id, lname, fname, mname
                    FROM user
                    WHERE user_id = $user_id AND status != 'deleted'
                    AND user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id)";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    
    function get_admins_and_employees($group_id) {
        $query = "(SELECT user_id, lname, fname, mname
                    FROM user
                    NATURAL JOIN user_role
                    NATURAL JOIN role
                    WHERE (role.role_id = 1 AND role.rolename = 'admin')
                        AND status != 'deleted'
                        AND user.user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id))
                UNION
                (SELECT user_id, lname, fname, mname
                    FROM user
                    WHERE department IS NOT NULL AND status != 'deleted'
                    AND user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id))";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    function get_admin_and_students($group_id) {
        $query = "(SELECT user_id, lname, fname, mname
                    FROM user
                    NATURAL JOIN user_role
                    NATURAL JOIN role
                    WHERE (role.role_id = 1 AND role.rolename = 'admin')
                        AND status != 'deleted'
                        AND user.user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id))
                UNION
                (SELECT user_id, lname, fname, mname
                    FROM user
                    WHERE course IS NOT NULL AND status != 'deleted'
                    AND user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id))";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    function get_employees_and_students($group_id) {
        $query = "(SELECT user_id, lname, fname, mname
                    FROM user
                    WHERE department IS NOT NULL AND status != 'deleted'
                    AND user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id))
                UNION
                (SELECT user_id, lname, fname, mname
                    FROM user
                    WHERE course IS NOT NULL AND status != 'deleted'
                    AND user_id NOT IN
                    (SELECT user.user_id FROM user
                     NATURAL JOIN subscriber
                     WHERE subscriber.group_id = $group_id))";
        $q = $this->db->query($query);
        return $q->result_array();
    }
}
?>
